import time
import uuid
import sqlalchemy
from sqlalchemy import select, insert, delete, update
from sqlalchemy.ext.asyncio import create_async_engine
import src.config as config
from src.models import User, Booking, UserWithoutPassword
import src.exceptions as exc


class AlchemyDB:

    def __init__(self, alchemy_url: str):
        self.metadata = sqlalchemy.MetaData()
        self.users = sqlalchemy.Table('users', self.metadata,
                                      sqlalchemy.Column('user_id', sqlalchemy.Uuid, primary_key=True),
                                      sqlalchemy.Column('username', sqlalchemy.VARCHAR(55), nullable=False),
                                      sqlalchemy.Column('password', sqlalchemy.LargeBinary, nullable=False),
                                      sqlalchemy.Column('created_at', sqlalchemy.Float, nullable=False),
                                      sqlalchemy.Column('updated_at', sqlalchemy.Float),
                                      )
        self.bookings = sqlalchemy.Table('bookings', self.metadata,
                                         sqlalchemy.Column('booking_id', sqlalchemy.Uuid, primary_key=True),
                                         sqlalchemy.Column('user_id', sqlalchemy.Uuid, nullable=False),
                                         sqlalchemy.Column('start_time', sqlalchemy.Float, nullable=False),
                                         sqlalchemy.Column('end_time', sqlalchemy.Float, nullable=False),
                                         sqlalchemy.Column('comment', sqlalchemy.VARCHAR(255)),
                                         sqlalchemy.Column('created_at', sqlalchemy.Float, nullable=False),
                                         sqlalchemy.Column('updated_at', sqlalchemy.Float),
                                         )
        self.engine = create_async_engine(alchemy_url, echo=True)

    async def connect(self) -> None:
        async with self.engine.begin() as conn:
            await conn.run_sync(self.metadata.create_all)
        await self.engine.dispose()

    # === Users methods ===

    async def get_users(self, password: bool = False) -> list[User]:
        async with self.engine.begin() as conn:
            raw_res = await conn.execute(select(self.users))
        await self.engine.dispose()
        raw_res = raw_res.fetchall()
        res = []
        for user in raw_res:
            if password:
                res.append(User(**user._mapping))
            else:
                res.append(UserWithoutPassword(**user._mapping))
        return res

    async def get_user(self, user_id: uuid.UUID, password: bool = False) -> User:
        if not isinstance(user_id, uuid.UUID):
            raise exc.InvalidParameterFormat("user_id must be type of uuid.UUID.")
        async with self.engine.begin() as conn:
            raw_res = await conn.execute(select(self.users).where(self.users.c.user_id == user_id))
        await self.engine.dispose()
        res = raw_res.fetchone()
        if res is None:
            raise exc.UserNotFound(f"id {user_id} not found in database.")
        if password:
            return User(**res._mapping)
        else:
            return UserWithoutPassword(**res._mapping)

    async def add_user(self, user: User) -> None:
        if not isinstance(user, User):
            raise exc.InvalidParameterFormat("user must be type of models.User.")
        async with self.engine.begin() as conn:
            await conn.execute(insert(self.users).values(**user.model_dump(by_alias=True)))
        await self.engine.dispose()

    async def update_user(self, updated_user: User) -> None:
        if not isinstance(updated_user, User):
            raise exc.InvalidParameterFormat("updated_user must be type of models.User.")
        updated_user.updated_at = time.time()
        async with self.engine.begin() as conn:
            await conn.execute(update(self.users).where(self.users.c.user_id == updated_user.id).
                               values(**updated_user.model_dump(by_alias=True)))
        await self.engine.dispose()

    async def del_user(self, user_id: uuid.UUID) -> None:
        if not isinstance(user_id, uuid.UUID):
            raise exc.InvalidParameterFormat("user_id must be type of uuid.UUID.")
        async with self.engine.begin() as conn:
            await conn.execute(delete(self.users).where(self.users.c.user_id == user_id))
            await conn.execute(delete(self.bookings).where(self.users.c.user_id == user_id))
        await self.engine.dispose()

    # === Booking methods ===

    async def get_bookings(self) -> list[Booking]:
        async with self.engine.begin() as conn:
            raw_res = await conn.execute(select(self.bookings))
        await self.engine.dispose()
        raw_res = raw_res.fetchall()
        res = []
        for booking in raw_res:
            res.append(Booking(**booking._mapping))
        return res

    async def get_user_bookings(self, user_id: uuid.UUID) -> list[Booking]:
        async with self.engine.begin() as conn:
            raw_res = await conn.execute(select(self.bookings).where(self.users.c.user_id == user_id))
        await self.engine.dispose()
        raw_res = raw_res.fetchall()
        res = []
        for booking in raw_res:
            res.append(Booking(**booking._mapping))
        return res

    async def get_booking(self, booking_id: uuid.UUID) -> Booking:
        if not isinstance(booking_id, uuid.UUID):
            raise exc.InvalidParameterFormat("booking_id must be type of uuid.UUID.")
        async with self.engine.begin() as conn:
            raw_res = await conn.execute(select(self.bookings).where(self.bookings.c.booking_id == booking_id))
        await self.engine.dispose()
        res = raw_res.fetchone()
        if res is None:
            raise exc.BookingNotFound(f"id {booking_id} not found in database.")
        return Booking(**res._mapping)

    async def add_booking(self, booking: Booking) -> None:
        if not isinstance(booking, Booking):
            raise exc.InvalidParameterFormat("booking must be type of models.Booking.")
        async with self.engine.begin() as conn:
            await conn.execute(insert(self.bookings).values(**booking.model_dump(by_alias=True)))
        await self.engine.dispose()

    async def update_booking(self, updated_booking: Booking) -> None:
        if not isinstance(updated_booking, Booking):
            raise exc.InvalidParameterFormat("updated_booking must be type of models.Booking.")
        updated_booking.updated_at = time.time()
        async with self.engine.begin() as conn:
            await conn.execute(update(self.users).where(self.bookings.c.booking_id == updated_booking.id).
                               values(**updated_booking.model_dump(by_alias=True)))
        await self.engine.dispose()

    async def del_booking(self, booking_id: uuid.UUID) -> None:
        if not isinstance(booking_id, uuid.UUID):
            raise exc.InvalidParameterFormat("booking_id must be type of uuid.UUID.")
        async with self.engine.begin() as conn:
            await conn.execute(delete(self.users).where(self.bookings.c.booking_id == booking_id))
            await conn.execute(delete(self.bookings).where(self.bookings.c.booking_id == booking_id))
        await self.engine.dispose()
